# -*- coding: utf-8 -*-
import math
import time
import os
from decimal import Decimal

import openpyxl
import platform
from openpyxl.styles import PatternFill, Font

from data.easyquotation_data import get_stock_data
from data.getdata import get_now_limit, get_history_limit, get_today_trend, get_history_trend, get_history_date, \
    get_history_down_limit, wencai_search

file = ''


def read_excel(file_path):
    global file
    filename = file_path

    # 判断是 windows 还是 linux
    if platform.system().lower() == 'windows':
        pass
    elif platform.system().lower() == 'linux':
        filename = filename.replace('\\', '/')

    workbook = openpyxl.load_workbook(filename)
    worksheet = workbook.get_sheet_by_name("Sheet1")  # 读取Sheet
    m_rows, m_cols = worksheet.max_row, worksheet.max_column
    red = "FFFF0000"
    cols = {}
    data = []
    for i in range(1, m_rows + 1):
        m_data = []
        none_count = 0  # none 计数
        for j in range(1, m_cols + 1):
            ce = worksheet.cell(row=i, column=j)
            # print(ce.value)
            # fill = ce.fill
            font = ce.font  # 字体的颜色

            # 设置返回的数据
            # if type(ce.fill.fgColor.value) == int:
            #     fgColorValue = '00000000'
            # else:
            #     fgColorValue = ce.fill.fgColor.value
            fgColorValue = ce.fill.fgColor.value
            if fgColorValue == '00000000':
                fgColorValue = 'ffffffff'  # 默认字体背景颜色
            defaultColor = '00000000'  # 默认字体颜色
            if font.color is not None:
                # print(font.color.rgb)
                cols = {'data': ce.value, 'color': font.color.value, 'background_color': fgColorValue}
            else:
                cols = {'data': ce.value, 'color': defaultColor, 'background_color': fgColorValue}
                if ce.value is None:
                    none_count = none_count + 1
                    if none_count >= 4:
                        cols = {'data_none': none_count, 'color': defaultColor, 'background_color': fgColorValue}

            # 设置合并单元格
            for merged_cells in worksheet.merged_cells:
                if ce.coordinate == merged_cells.start_cell.coordinate:
                    cols['span'] = merged_cells.size['columns']  # 添加合并单元格属性

            # cols_data = [cols]
            m_data.append(cols)
            # if fill.start_color.rgb == "FFFFFF00" and ce.value != None:
            #     yellow.append(ce.value)
            # if font.color.rgb == "FFFF0000":
            #     red.append(ce.value)
        data.append(m_data)
    return data


def read_excel_only(file_path):
    global file
    filename = file_path

    # 判断是 windows 还是 linux
    if platform.system().lower() == 'windows':
        pass
    elif platform.system().lower() == 'linux':
        filename = filename.replace('\\', '/')

    workbook = openpyxl.load_workbook(filename)
    worksheet = workbook.get_sheet_by_name("Sheet1")  # 读取Sheet
    m_rows, m_cols = worksheet.max_row, worksheet.max_column

    data = []
    for i in range(1, m_rows + 1):
        m_data = []
        none_count = 0  # none 计数
        for j in range(1, m_cols + 1):
            ce = worksheet.cell(row=i, column=j)
            m_data.append(ce.value)
        data.append(m_data)
    return data


def update_excel(file_path):
    """根据股票代码更新excel"""
    global file
    filename = file_path

    workbook = openpyxl.load_workbook(filename)
    worksheet = workbook.get_sheet_by_name("Sheet1")  # 读取Sheet
    m_rows, m_cols = worksheet.max_row, worksheet.max_column
    data = get_data()
    # print(file_path)
    for i in range(1, m_rows + 1):
        ce = worksheet.cell(row=i, column=2)
        # print(ce.value)
        # //判断是否是数字
        if str(ce.value).isdigit():
            code_data = get_code_data(str(ce.value), data)
            # print(code_data)
            if len(code_data) == 5:
                worksheet.cell(row=i, column=3).value = code_data[0]
                worksheet.cell(row=i, column=4).value = code_data[1]
                worksheet.cell(row=i, column=5).value = code_data[2]
                worksheet.cell(row=i, column=6).value = code_data[3]
                # worksheet.cell(row=i, column=7).value = code_data[4]

    workbook.save(file_path)


def update_excel_history(file_path):
    """根据股票代码更新excel"""

    # print(file_path)
    today = time.strftime("%Y-%m-%d", time.localtime())  # 当前日期
    first = True  # 股票名称 打板时间 第几板 成交额 只更新一遍
    global file
    filename = file_path

    workbook = openpyxl.load_workbook(filename)
    worksheet = workbook.get_sheet_by_name("Sheet1")  # 读取Sheet
    m_rows, m_cols = worksheet.max_row, worksheet.max_column
    filename = os.path.basename(filename)
    date = filename.split('.')[0]  # 根据文件名获取日期
    data = None
    count = 6
    while count > 0:
        count = count - 1
        if today == date:
            update_excel(file_path)
            print(file_path, 'finish')
            return
        else:
            _data = get_history_limit(date)
        if _data is not None:
            data = _data['list']  # 获取date历史涨停数据
            break
        time.sleep(3)
    if count == 0:
        print('获取' + date + '历史涨停数据失败')
    # data = get_history_limit(date)['list']  # 获取date历史涨停数据
    # print(data)

    # 获取第二天日期
    dates = get_history_date(100)
    if dates is None:
        return
    if date not in dates:
        raise ValueError('没有发现日期')
        return
    m_index = dates.index(date)  # 获取第二天的日期的索引
    codes_up_limit = []  # 涨停池
    codes_down_limit = []  # 跌停池
    # today = time.strftime("%Y-%m-%d", time.localtime())  # 当前日期
    m_cols = 10
    # 遍历涨停日期后每个交易日的涨幅
    for m_date in dates[:m_index + 1]:

        if today != m_date:  # 跳过今天不更新
            count = 6
            while count > 0:
                count = count - 1
                _codes_up_limit = get_history_limit(m_date)  # 第二天的涨停票
                if _codes_up_limit is not None:
                    codes_up_limit = _codes_up_limit['list']  # 第二天的涨停票
                    break
            if count == 0:
                print(filename, date, "获取历史涨停数据失败")
            count = 6
            while count > 0:
                count = count - 1
                _codes_down_limit = get_history_down_limit(m_date)  # 第二天的跌停票
                if _codes_down_limit is not None:
                    codes_down_limit = _codes_down_limit['list']
                    break
            if count == 0:
                print(filename, date, "获取历史跌停数据失败")
        else:
            continue

        if str(worksheet.cell(row=2, column=m_cols).value) == m_date:  # 已经更新过的日期跳过
            break
        else:
            worksheet.insert_cols(idx=m_cols)  # 插入新的一列
            # workbook.save(file_path)

        data_wencai = searchWencai(m_date + '涨幅')  # 获取指定date日期的涨幅数据
        # 遍历设置Excel数据
        for i in range(1, m_rows + 1):
            ce = worksheet.cell(row=i, column=2)

            # 判断是否是数字(股票代码)
            if str(ce.value).isdigit():
                code_data = get_code_data(str(ce.value), data)
                if len(code_data) == 5 and first:
                    worksheet.cell(row=i, column=3).value = code_data[0]
                    worksheet.cell(row=i, column=4).value = code_data[1]
                    worksheet.cell(row=i, column=5).value = code_data[2]
                    worksheet.cell(row=i, column=6).value = code_data[3]
                    # first = False
                else:  # 不在涨停板里面，补充数据
                    # count = 6
                    # while count > 0:
                    #     count = count - 1
                    #     _data = get_stock_data(str(ce.value))
                    #     if _data is not None:
                    #         break
                    # worksheet.cell(row=i, column=3).value = _data[str(ce.value)]['name']
                    try:
                        worksheet.cell(row=i, column=3).value = \
                            data_wencai[(data_wencai.code == str(ce.value))]['股票简称'].tolist()[0]
                    except Exception as error:
                        print(data_wencai[(data_wencai.code == str(ce.value))]['股票简称'].tolist())
                # 获取m_date日期的数据
                date = m_date.replace('-', '')  # 转换下日期格式
                try:
                    rise = data_wencai[(data_wencai.code == str(ce.value))]['涨跌幅:前复权[' + date + ']'].tolist()[0]
                except Exception as error:
                    print(data_wencai[(data_wencai.code == str(ce.value))]['涨跌幅:前复权[' + date + ']'].tolist())
                rise = Decimal(rise).quantize(Decimal("0.01"), rounding="ROUND_HALF_UP")  # 小数点后取2位四舍五入
                if math.isnan(rise):
                    rise = 0
                worksheet.cell(row=i, column=m_cols).value = rise  # 涨幅

                # 设置涨幅字体颜色
                if rise > 0:
                    worksheet.cell(row=i, column=m_cols).font = Font(color='fa4949')  # 红色字体
                elif rise < 0:
                    worksheet.cell(row=i, column=m_cols).font = Font(color='00c800')  # 绿色字体

                worksheet.cell(row=i, column=m_cols).fill = PatternFill("solid", fgColor="00000000")  # 清楚之前的背景色

                # 设置涨停背景色
                for m_code in codes_up_limit:
                    if m_code.count(str(ce.value)):
                        worksheet.cell(row=i, column=m_cols).fill = PatternFill("solid", fgColor="fa4949")
                        worksheet.cell(row=i, column=m_cols).font = Font(color='000000')  # 黑色字体
                        break

                # 设置跌停背景色
                for m_code in codes_down_limit:
                    if m_code.count(str(ce.value)):
                        worksheet.cell(row=i, column=m_cols).fill = PatternFill("solid", fgColor="49c8fa")
                        worksheet.cell(row=i, column=m_cols).font = Font(color='000000')  # 黑色字体
                        break

            elif str(ce.value) == '股票代码':
                worksheet.cell(row=i, column=m_cols).number_format = '@'  # 设置单元格为文本格式
                worksheet.cell(row=i, column=m_cols).value = m_date

        m_cols = m_cols + 1
        first = False
        # workbook.save(file_path)
    workbook.save(file_path)
    print(file_path, 'finish')


def write_excel(cell, row, column, value):
    """写入数据"""
    cell(row, column, value)


def get_data():
    """获取涨停数据"""
    lists = get_now_limit()['list']
    return lists


def get_code_data(code, lists):
    """获取指定代码的数据"""
    data = []
    for m_list in lists:
        m_index = m_list.count(code)

        if m_index != 0:
            # print(m_list)
            data.append(m_list[1])
            data.append(time.strftime("%H:%M:%S", time.localtime(m_list[6])))
            data.append(m_list[9])
            data.append(float('%.2f' % (m_list[13] / 100000000)))
            data.append(format(float('%.2f' % m_list[4]), '.2f'))  # 股票名称、时间、第几板、成交额、涨幅
            # lists.remove(m_list)  # 删除匹配到的数据，减少下次索引时间。
            break
    return data


def searchWencai(date):
    """问财获取数据"""
    data = wencai_search(date)
    return data


def is_number(s):
    try:
        float(s)
        return True
    except ValueError:
        pass

    try:
        import unicodedata
        unicodedata.numeric(s)
        return True
    except (TypeError, ValueError):
        pass

    return False
